package com.lipaluma.test.db.internal.clean;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;

import com.lipaluma.test.db.annotations.clean.CleanAfterManually;
import com.lipaluma.test.db.annotations.clean.CleanSchema;
import com.lipaluma.test.db.annotations.clean.CleanTables;
import com.lipaluma.test.db.exceptions.CleanException;
import com.lipaluma.test.db.exceptions.DBTestIllegalUseException;
import com.lipaluma.test.db.internal.DbUtils;
import com.lipaluma.test.db.internal.DialectEnum;
import com.lipaluma.test.db.internal.batch.BatchUtils;
import com.lipaluma.test.db.internal.batch.Param;

public class CleanUtils {
	private static Logger LOG = LoggerFactory.getLogger(CleanUtils.class);
	private static List<String> tablesCleaned;
	
	private static List<Param> params = new ArrayList<Param>();

	public static void doClean(CleanSchema cleanSchema) throws IOException, FileNotFoundException, SQLException {
		if (cleanSchema != null) {
			String[] schemas = cleanSchema.value();
			DataSource ds = getDataSource(cleanSchema.dataSource());
			if (ArrayUtils.isEmpty(schemas)) {
				long start = System.currentTimeMillis();
				if(cleanSchema.strategy().equals(CleanStrategy.DELETE))
					doCleanSchemaByDelete(ds, null);
				else {
					DialectEnum dialect = getDialect(cleanSchema.dataSource(), cleanSchema.dialect());
					doCleanSchemaByTruncate(ds, dialect, null);
				}
				LOG.debug("Temps d'exécution du clean du schéma : "+(System.currentTimeMillis() - start)+" ms");
			} else {
				for (String schema : schemas) {
					long start = System.currentTimeMillis();
					if(cleanSchema.strategy().equals(CleanStrategy.DELETE))
						doCleanSchemaByDelete(ds, schema);
					else {
						DialectEnum dialect = getDialect(cleanSchema.dataSource(), cleanSchema.dialect());
						doCleanSchemaByTruncate(ds, dialect, schema);
					}
					LOG.debug("Temps d'exécution du clean "+schema+": "+(System.currentTimeMillis() - start)+" ms");
					LOG.debug("schema : "+schema+", truncate des tables : "+tablesCleaned);
				}
			}
		}
	}

	public static void doClean(CleanTables cleanTables) throws IOException, FileNotFoundException {
		if (cleanTables != null) {
			String[] tables = cleanTables.tables();
			if (ArrayUtils.isEmpty(tables)) {
				throw new DBTestIllegalUseException("tables must be defined in CleanTables Annotation.");
			} else {
				DataSource ds = getDataSource(cleanTables.dataSource());
				if(cleanTables.strategy().equals(CleanStrategy.DELETE))
					doCleanTablesByDelete(ds, cleanTables.schema(), tables);
				else {
					DialectEnum dialect = getDialect(cleanTables.dataSource(), cleanTables.dialect());
					doCleanTablesByTruncate(ds, dialect, cleanTables.schema(), tables);
				}
			}
		}
	}	

	public static void doCleanAfterManually(CleanAfterManually.List list) throws SQLException, IOException {
		if(list == null)
			return;
		CleanAfterManually[] cleans = list.value();
		for (CleanAfterManually clean : cleans) {
			doCleanAfterManually(clean);
		}
	}

	public static void doCleanAfterManually(CleanAfterManually clean) throws SQLException, IOException {
		if(clean == null)
			return;
		
		DataSource ds = getDataSource(clean.dataSource());				
		for(String filename : clean.files()) {
			BatchUtils.executeBatch(ds, filename, params.toArray(new Param[0]));
		}
		for(String request : clean.request()) {
			BatchUtils.executeBatch(ds, new StringReader(request), params.toArray(new Param[0]));
		}
		
	}
	
	public static void doCleanTablesByDelete(DataSource ds, String schema, String... tableNames) throws IOException {
		Connection connection = DataSourceUtils.getConnection(ds);
		for (String table : tableNames) {
			doCleanTableByDelete(connection, schema, table);
		}		
	}

	public static void doCleanTablesByTruncate(DataSource ds, DialectEnum dialect, String schema, String... tableNames) throws IOException {
		Connection connection = DataSourceUtils.getConnection(ds);
		for (String table : tableNames) {
			dialect.doCleanTableByTruncate(connection, schema, table);
		}		
	}

	public static void doCleanSchemaByDelete(DataSource ds, String schema) throws SQLException, IOException {
		tablesCleaned = new ArrayList<String>();
		Connection connection = DataSourceUtils.getConnection(ds);
		DatabaseMetaData dbmd = connection.getMetaData();
		List<String> tableNames = getTableNames(dbmd, schema);
		Map<String, List<String>> mapDependencies = getTableDependencies(schema, dbmd, tableNames);
		for (String table : tableNames) {
			cleanTableAfterDependencies(connection, schema, table, mapDependencies);
		}		
	}

	public static void doCleanSchemaByTruncate(DataSource ds, DialectEnum dialect, String schema) throws SQLException, IOException {
		tablesCleaned = new ArrayList<String>();
		Connection connection = DataSourceUtils.getConnection(ds);
		DatabaseMetaData dbmd = connection.getMetaData();
		List<String> tableNames = getTableNames(dbmd, schema);
		Map<String, List<String>> mapDependencies = getTableDependencies(schema, dbmd, tableNames);
		for (String table : tableNames) {
			cleanTableAfterDependenciesByTruncate(connection, schema, table, mapDependencies, dialect);
		}		
	}

	public static void doCleanTableByDelete(Connection connection, String schema, String table) {
		try {
			PreparedStatement ps;
			if(StringUtils.isNotBlank(schema)) 
				ps = connection.prepareStatement("DELETE FROM "+schema+"."+table+";");
			else
				ps = connection.prepareStatement("DELETE FROM "+table+";");
			ps.execute();
		} catch (SQLException e) {
			throw new CleanException("erreur lors du clean de la table "+table, e);
		}
	}

	public static void initParams() {
		params = new ArrayList<Param>();
	}

	public static void setExpectedValueForKey(String key, String value) {
		Param param = new Param(key, value);
		int index = params.indexOf(param);
		if(index == -1) {
			params.add(param);
		} else {
			params.set(index, param);
		}
	}

	private static void cleanTableAfterDependencies(Connection connection, String schema, String table, Map<String, List<String>> mapDependencies) {
		List<String> dependencies = mapDependencies.get(table);
		for (String dependencyTable : dependencies) {
			cleanTableAfterDependencies(connection, schema, dependencyTable, mapDependencies);
		}
		if(!tablesCleaned.contains(table)){
			doCleanTableByDelete(connection, schema, table);
			tablesCleaned.add(table);
		}
	}

	private static void cleanTableAfterDependenciesByTruncate(Connection connection, String schema, String table, Map<String, List<String>> mapDependencies, DialectEnum dialect) {
		List<String> dependencies = mapDependencies.get(table);
		for (String dependencyTable : dependencies) {
			cleanTableAfterDependenciesByTruncate(connection, schema, dependencyTable, mapDependencies, dialect);
		}
		if(!tablesCleaned.contains(table)){
			dialect.doCleanTableByTruncate(connection, schema, table);
			tablesCleaned.add(table);
		}
	}

	private static Map<String, List<String>> getTableDependencies(String schema, DatabaseMetaData dbmd, List<String> tableNames) throws SQLException {
		Map<String, List<String>> mapTableDependencies = new HashMap<String, List<String>>();
		for (String table : tableNames) {
			ResultSet rs = dbmd.getExportedKeys(schema, null, table);
			List<String> dependencies = new ArrayList<String>();
			while(rs.next()) {
				dependencies.add(rs.getString(7));
			}
			mapTableDependencies.put(table, dependencies);
		}
		return mapTableDependencies;
	}

	private static List<String> getTableNames(DatabaseMetaData dbmd, String schema) throws SQLException {
		String[] types = {"TABLE"};
		ResultSet rs = dbmd.getTables(schema, null, "%", types);
		List<String> tableNames = new ArrayList<String>();
		while(rs.next()) {
			tableNames.add(rs.getString(3));
		}
		return tableNames;
	}

	private static DataSource getDataSource(String dataSourceName) throws IOException {
		DataSource ds;
		if(dataSourceName.isEmpty())
			ds = DbUtils.getDataSource();
		else 
			ds = DbUtils.getDataSourceWithName(dataSourceName);
		return ds;
	}
	
	private static DialectEnum getDialect(String dataSourceName, DialectEnum defaultDialect) {
		DialectEnum dialect = DbUtils.getDialect(dataSourceName);
		if (dialect == null)
			return defaultDialect;
		return dialect;
	}

}
